using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using Sanchay;
namespace Sanchay
{
    /*
     TODO: Implement the following
     1. Update Record
     2. Delete Record
     3. Status Strip
    */
    public partial class SparePurchaseRegister : Form
    {
        //private int _srNo = 1;  // for auto increament of the Sr. no.
        AutoCompleteStringCollection autoCompleteStrings;
        string temp = "";
        bool flag = false;
        bool invoiceFlag = false;

        public SparePurchaseRegister()
        {
            InitializeComponent();
        }

        private void SparePurchaseRegister_Load(object sender, EventArgs e)
        {
            dgvPurchaseInvoice.Rows[0].Cells[0].Value = "";
            dgvPurchaseInvoice.Rows[0].Cells[1].Value = "";
            dgvPurchaseInvoice.Rows[0].Cells[2].Value = "";
            dgvPurchaseInvoice.Rows[0].Cells[3].Value = "";
            dgvPurchaseInvoice.Rows[0].Cells[4].Value = "";

            autoCompleteStrings = new AutoCompleteStringCollection();
            autoCompleteStrings.Add("");
            AutoSuggestForSupplier();
        }

        private void ResetEntryGrid()
        {
          
            //for (int i = 0; i < 10; i++)
            //{
            //    dgvPurchaseInvoice.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            //    dgvPurchaseInvoice.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            //    dgvPurchaseInvoice.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            //    dgvPurchaseInvoice.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
            //    dgvPurchaseInvoice.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;


            //    dgvPurchaseInvoice.Rows.Add();
            //    //dgvPurchaseInvoice.Rows[i].Cells[0].Value = (_srNo).ToString();
            //    dgvPurchaseInvoice.Rows[i].Cells[0].Value = "";
            //    dgvPurchaseInvoice.Rows[i].Cells[1].Value = "";
            //    dgvPurchaseInvoice.Rows[i].Cells[2].Value = "";
            //    dgvPurchaseInvoice.Rows[i].Cells[3].Value = "";
            //    dgvPurchaseInvoice.Rows[i].Cells[4].Value = "";

            //    //_srNo += 1;
            //    if (dgvPurchaseInvoice.Rows.Count == 10)
            //        dgvPurchaseInvoice.AllowUserToAddRows = false;
            //}
            //dgvPurchaseInvoice.Rows[0].Cells[0].Selected = true; // Foucus to the first cell of the Grid
        }

        private void ClearEntryGrid()
        {
            try
            {
                int rowCount = dgvPurchaseInvoice.RowCount;
                for (int i = 0; i < rowCount; i++)
                {
                    dgvPurchaseInvoice.Rows.RemoveAt(0);

                }

                dgvPurchaseInvoice.AllowUserToAddRows = dgvPurchaseInvoice.RowCount <= 5;
            }
            catch (Exception ex)
            { 
                
            }
        }

        private void ClearSummeryGrid()
        {
            try
            {
                //for (int i = 0; i < dgvInvoiceSummery.Rows.Count; i++)
                //{
                //    dgvInvoiceSummery.Rows.Clear();

                //}
                dgvInvoiceSummery.DataSource = "";
            }
            catch (Exception ex)
            { 

            }
        }

        private void RefreshSummeryGrid()
        {
            DataAccess da = new DataAccess();
           
            string query = "SELECT [Parts No.], [Parts Name], [Quantity],[Unit Price], [Amount] from vw_SummeryGrid WHERE InvoiceNo='" + txtPurchaseInvoiceNo.Text + "'";

            DataTable dt = da.SelectCommand(query);
            dgvInvoiceSummery.DataSource = dt.DefaultView;
            decimal totalInvoiceValue=0;

            for (int i = 0; i < dgvInvoiceSummery.Rows.Count; i++)
            {
                totalInvoiceValue += decimal.Parse(dgvInvoiceSummery.Rows[i].Cells[4].Value.ToString());
            }

            lblTotalInvoiceValue.Text = "Total: `" + " " + General.ChangeIntoIndianCurrencyFormat(totalInvoiceValue);

            dgvInvoiceSummery.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            dgvInvoiceSummery.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleLeft;
            dgvInvoiceSummery.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;
            dgvInvoiceSummery.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleRight;

            dgvInvoiceSummery.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter;
            dgvInvoiceSummery.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight;
            dgvInvoiceSummery.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomRight;
            //dgvInvoiceSummery.Columns[0].FillWeight = 4;
            dgvInvoiceSummery.Columns[0].FillWeight = 17;
            dgvInvoiceSummery.Columns[1].FillWeight = 45;
            dgvInvoiceSummery.Columns[2].FillWeight = 8;
            dgvInvoiceSummery.Columns[3].FillWeight = 15;
            dgvInvoiceSummery.Columns[4].FillWeight = 15;
            //GiveSerialNumbersToSummeryGrid();


        }

        private void AutoSuggestForSupplier()
        {
            // Auto Complete for Supplier
            DataAccess da = new DataAccess();
            SqlConnection conn = da.conn;
            SqlCommand cmd = new SqlCommand("SELECT Name FROM Supplier", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();


            while (dr.Read())
            {
                txtSupplier.AutoCompleteCustomSource.Add(dr.GetString(0));

            }
            txtSupplier.AutoCompleteMode = AutoCompleteMode.Suggest;
            txtSupplier.AutoCompleteSource = AutoCompleteSource.CustomSource;
            conn.Close();
        }

        private void dgvPurchaseInvoice_EditingControlShowing(object sender, DataGridViewEditingControlShowingEventArgs e)
        {
            TextBox t = (TextBox)e.Control;
            t.TextChanged += new EventHandler(t_TextChanged);
        }

        void t_TextChanged(object sender, EventArgs e)
        {
            TextBox t = (TextBox)sender;
            if (dgvPurchaseInvoice.CurrentCell.ColumnIndex == 0)  // to prevent autosuggest on other textboxes in the grid
            {

                if (t.Text.Length > 3)
                {
                    return;
                }
                if (t.Text.Length == 3)
                {
                    if (temp != t.Text)
                        flag = false;

                    if (flag == false)
                    {
                        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\db.mdf;Integrated Security=True;User Instance=True");
                        try
                        {

                            string query = "SELECT PartsNo FROM SpareParts WHERE PartsNo LIKE '" + t.Text + "%'";

                            SqlDataReader dr;
                            SqlCommand cmd = new SqlCommand(query, conn);
                            conn.Open();
                            dr = cmd.ExecuteReader();
                            while (dr.Read())
                            {
                                autoCompleteStrings.Add(dr.GetString(0));
                            }
                            t.AutoCompleteMode = AutoCompleteMode.Suggest;
                            t.AutoCompleteSource = AutoCompleteSource.CustomSource;
                            t.AutoCompleteCustomSource = autoCompleteStrings;
                            temp = t.Text;
                            flag = true;
                        }
                        catch (Exception ex)
                        {
                            //MessageBox.Show("An error occured. Please try again.");
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }
            }
        }
        //}
        private void dgvPurchaseInvoice_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            //
            //  Auto filling the Parts Name based on Parts No. column value
            //

            try
            {
             
                
                if (e.ColumnIndex == 0 && dgvPurchaseInvoice.Rows[e.RowIndex].Cells[0].Value.ToString() != "")
                {
                    SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\db.mdf;Integrated Security=True;User Instance=True");

                    string partsNo = Convert.ToString(dgvPurchaseInvoice.Rows[e.RowIndex].Cells[0].Value);
 

                    //if (CheckDuplicateEntry(partsNo) == true)
                    //{
                    //    MessageBox.Show("Duplicate Entry");
                    //    dgvPurchaseInvoice.Rows[e.RowIndex].Cells[e.ColumnIndex].Value = "";
                    //    //dgvPurchaseInvoice.CurrentCell = dgvPurchaseInvoice.Rows[e.RowIndex].Cells[e.ColumnIndex];
                    //    //dgvPurchaseInvoice.BeginEdit(true);

                    //    return;
                    //}

                    string query = "SELECT PartsName FROM SpareParts WHERE PartsNo='" + partsNo + "'";
                    SqlDataReader dr;
                    SqlCommand cmd = new SqlCommand(query, conn);
                    autoCompleteStrings = new AutoCompleteStringCollection();

                    conn.Open();
                    dr = cmd.ExecuteReader();
                    if (dr.HasRows == false)
                    {
                        //if (partsNo.Length >= 3)
                        //{
                            AddSpareParts objAddSpareParts = new AddSpareParts(dgvPurchaseInvoice, dgvPurchaseInvoice.CurrentRow.Index, partsNo);
                            objAddSpareParts.ShowDialog();
                        //}
                    }
                    while (dr.Read())
                    {
                        dgvPurchaseInvoice.Rows[e.RowIndex].Cells[1].Value = dr.GetString(0);
                    }
                    conn.Close();
                }
                int itemQuantity=0;
                decimal unitPrice = 0;
                decimal totalPrice = 0;

                if (e.ColumnIndex == 2 || e.ColumnIndex == 3 || e.ColumnIndex == 4)
                {
                    if (Convert.ToString(dgvPurchaseInvoice.Rows[e.RowIndex].Cells[2].Value) != "")
                    {
                         itemQuantity = Convert.ToInt32(dgvPurchaseInvoice.Rows[e.RowIndex].Cells[2].Value);
                    }

                    if (Convert.ToString(dgvPurchaseInvoice.Rows[e.RowIndex].Cells[3].Value) != "")
                    {
                         unitPrice = Convert.ToDecimal(dgvPurchaseInvoice.Rows[e.RowIndex].Cells[3].Value);
                         totalPrice = decimal.Round(itemQuantity * unitPrice, 2);
                    }
                    if (totalPrice == 0)
                        dgvPurchaseInvoice.Rows[e.RowIndex].Cells[4].Value = "";
                    else
                        dgvPurchaseInvoice.Rows[e.RowIndex].Cells[4].Value = totalPrice;
                 
                    //int.TryParse(dgvPurchaseInvoice.Rows[e.RowIndex].Cells[2].Value.ToString(), out itemQuantity);
                    //decimal.TryParse(dgvPurchaseInvoice.Rows[e.RowIndex].Cells[3].Value.ToString(), out unitPrice);
                    //decimal.TryParse(dgvPurchaseInvoice.Rows[e.RowIndex].Cells[4].Value.ToString(), out totalAmount);

                }
                
            }
            catch (Exception ex)
            {

            }
            finally
            {
                
            }
        }

        private bool CheckDuplicateEntry(string partsNo)
        {
            int count = 0;
            for (int i = 0;  i < dgvPurchaseInvoice.Rows.Count; i++)
            {
                if(dgvPurchaseInvoice.Rows[i].Cells[0].Value.ToString()!="")
                    count+=1;
                
               
            }
            for (int j = 0; j < count-1; j++)
            {
                if (dgvPurchaseInvoice.Rows[j].Cells[0].Value.ToString() == partsNo)
                    return true;
            }
            return false;
        }

        // Save Button
        private void pictureBox2_Click_1(object sender, EventArgs e)
        {
            if (ValidateForm())
            {
                    DataAccess da = new DataAccess();

                    //Preventing duplicate Purchase Invoice                    
                    if (invoiceFlag == false)
                    {
                        AddSupplierIfNotExists();
                        if (InsertPurchaseInvoiceDetails(da) != 0)
                        {
                            invoiceFlag = true;
                        }
                    }

                    InsertPurchaseInvoiceItemDetails(da);
                    UpdateInventory(da);
                    
                    RefreshSummeryGrid();
                    ClearEntryGrid();
                    ResetEntryGrid();
            }
        }

        private bool ValidateForm()
        {
            txtPurchaseInvoiceNo.Focus();
            bool flag = false;
            // Invoice Number
            if (txtPurchaseInvoiceNo.Text == "")
            {
                txtPurchaseInvoiceNo.BackColor = Color.Pink;
                return false;
            }

            // Validate Entry Grid
            int columnCount = dgvPurchaseInvoice.ColumnCount;

            int count = dgvPurchaseInvoice.Rows.Count;

            for (int i = 0; i < dgvPurchaseInvoice.Rows.Count; i++)
            {
                
                bool emptyRow = CheckIfRowIsEmpty(i);

                for (int j = 0; j < columnCount; j++)
                {
                    bool IsNumeric = true;
                   

                    if (j == 2 || j == 3 || j == 4) // Validate Numeric
                    {
                        IsNumeric = Validation.CheckIfNumeric(dgvPurchaseInvoice.Rows[i].Cells[j].Value);
                    }

                    bool IsEmpty = Validation.CheckIfEmpty(dgvPurchaseInvoice.Rows[i].Cells[j].Value);
                    bool IsNull = Validation.CheckIfNull(dgvPurchaseInvoice.Rows[i].Cells[j].Value);

                    if (!emptyRow )
                    {
                        if (IsNull || IsEmpty || !IsNumeric)
                        {
                            dgvPurchaseInvoice.Rows[i].Cells[j].Style.BackColor = Color.Pink;
                            flag = true;

                        }
                        else
                            dgvPurchaseInvoice.Rows[i].Cells[j].Style.BackColor = Color.White;
                    }
                   
               
                }

                    if (emptyRow)
                    {
                        if (i != dgvPurchaseInvoice.Rows.Count)
                        {
                            try
                            {

                                dgvPurchaseInvoice.Rows.RemoveAt(i);
                                --i;
                            }
                            catch (Exception e)
                            {
                                dgvPurchaseInvoice.Rows[i].Cells[0].Style.BackColor = Color.White;
                                dgvPurchaseInvoice.Rows[i].Cells[1].Style.BackColor = Color.White;
                                dgvPurchaseInvoice.Rows[i].Cells[2].Style.BackColor = Color.White;
                                dgvPurchaseInvoice.Rows[i].Cells[3].Style.BackColor = Color.White;
                                dgvPurchaseInvoice.Rows[i].Cells[4].Style.BackColor = Color.White;
                            }

                        }
                        
                    }
            }

            if (flag==true)
            {
                return false;
            }
            return true;
        }

        private bool CheckIfRowIsEmpty(int i)
        {
            //if (dgvPurchaseInvoice.Rows[i].Cells[0].Value == null)
            //    dgvPurchaseInvoice.Rows[i].Cells[0].Value = "";
            //if (dgvPurchaseInvoice.Rows[i].Cells[1].Value == null)
            //    dgvPurchaseInvoice.Rows[i].Cells[1].Value = "";
            //if (dgvPurchaseInvoice.Rows[i].Cells[2].Value == null)
            //    dgvPurchaseInvoice.Rows[i].Cells[2].Value = "";
            //if (dgvPurchaseInvoice.Rows[i].Cells[3].Value == null)
            //    dgvPurchaseInvoice.Rows[i].Cells[3].Value = "";
            //if (dgvPurchaseInvoice.Rows[i].Cells[4].Value == null)
            //    dgvPurchaseInvoice.Rows[i].Cells[4].Value = "";

            if (Convert.ToString(dgvPurchaseInvoice.Rows[i].Cells[0].Value) == "" && Convert.ToString(dgvPurchaseInvoice.Rows[i].Cells[1].Value) == "" && Convert.ToString(dgvPurchaseInvoice.Rows[i].Cells[2].Value) == "" && Convert.ToString(dgvPurchaseInvoice.Rows[i].Cells[3].Value) == "" && Convert.ToString(dgvPurchaseInvoice.Rows[i].Cells[4].Value) == "")
            {
                return true;
            }
            return false;
        }

        private void AddSupplierIfNotExists()
        {
            DataAccess da = new DataAccess();
            bool isSupplierExists = CheckIfSupplierExists(da);
            if (isSupplierExists == false)
            {
                string query = "INSERT INTO Supplier values ('" + txtSupplier.Text + "' , '', '')";
                try
                {
                    if (txtSupplier.Text != "")
                    {
                        da.DoCommand(query);
                    }
                }
                catch (Exception ex)
                {

                }

            }
        }

        /// <summary>
        /// Checking if Supplier exists
        /// </summary>
        private bool CheckIfSupplierExists(DataAccess da)
        {
            bool isSupplierExists;
            string query = "SELECT Name FROM Supplier WHERE Name ='" + txtSupplier.Text + "'";
            DataTable dt = da.SelectCommand(query);
            if (dt.Rows.Count != 0)
            {
                isSupplierExists = true;
            }
            else
            {
                isSupplierExists = false;
            }
            return isSupplierExists;
        }

        private int InsertPurchaseInvoiceDetails(DataAccess da)
        {

            string query;
            // SpareParts Purchase Invoice No
            SqlParameter[] param = new SqlParameter[3];
            param[0] = new SqlParameter("InvoiceNo", txtPurchaseInvoiceNo.Text);
            param[1] = new SqlParameter("InvoiceDate", dateTimePicker1.Value.ToShortDateString());
            param[2] = new SqlParameter("SupplierId", GetSupplierIdByName(da, txtSupplier.Text));
            //param[3] = new SqlParameter("InvoiceTotalValue", 1000);

            query = "INSERT INTO SparePartsPurchaseRegister (InvoiceNo, InvoiceDate, SupplierId) VALUES (@InvoiceNo, @InvoiceDate, @SupplierId)";
            return da.DoCommand(query, param);
        }

        /// <summary>
        /// Update Inventory
        /// </summary>
        /// <param name="da"></param>
        private void UpdateInventory(DataAccess da)
        {
            string query;
            try
            {
                for (int i = 0; i < dgvPurchaseInvoice.Rows.Count; i++)
                {
                    if (dgvPurchaseInvoice.Rows[i].Cells[0].Value.ToString() != "")
                    {
                        int currentQty = Convert.ToInt16(dgvPurchaseInvoice.Rows[i].Cells[2].Value);
                        int rowsAffacted = 0;
                        if (dgvPurchaseInvoice.Rows[i].Cells[0].Value.ToString() != "")
                        {
                            string partsNo = dgvPurchaseInvoice.Rows[i].Cells[0].Value.ToString();
                            int availableQty = GetAvailableQty(da, partsNo);
                            if (availableQty != -1)// If not Null
                            {
                                query = "UPDATE InventoryMaster SET isCurrent=0 WHERE PartsNo='" + partsNo + "' AND isCurrent=1";
                                da.DoCommand(query);
                            }
                            else
                            {
                                availableQty = 0;
                            }

                            // Purchase Invoice Item Details
                            SqlParameter[] param = new SqlParameter[9];
                            param[0] = new SqlParameter("PartsNo", partsNo);
                            param[1] = new SqlParameter("Qty", currentQty);
                            param[2] = new SqlParameter("AvailableQty", availableQty + currentQty);
                            param[3] = new SqlParameter("TransactionDate", DateTime.Now);
                            param[4] = new SqlParameter("isCurrent", 1);
                            param[5] = new SqlParameter("TransactionType", 1);
                            param[6] = new SqlParameter("InvoiceNo", txtPurchaseInvoiceNo.Text);
                            param[7] = new SqlParameter("UnitPrice", Convert.ToDouble(dgvPurchaseInvoice.Rows[i].Cells[3].Value));
                            param[8] = new SqlParameter("TotalPrice", Convert.ToDouble(dgvPurchaseInvoice.Rows[i].Cells[4].Value));

                            query = "INSERT INTO InventoryMaster (PartsNO, Qty, AvailableQty, TransactionDate,isCurrent,TransactionType,InvoiceNo,UnitPrice,TotalPrice) VALUES (@PartsNO, @Qty, @AvailableQty, @TransactionDate,@isCurrent,@TransactionType,@InvoiceNo,@UnitPrice,@TotalPrice)";
                            rowsAffacted = da.DoCommand(query, param);
                            //toolStripStatusLabel.Text = rowsAffacted.ToString() + " Parts added successfully.";

                        }

                    }
                }
            }

            catch (Exception ex)
            {

            }

        }

        private void GiveSerialNumbersToSummeryGrid()
        {
            int count = 0;
            foreach (DataGridViewRow dr in dgvInvoiceSummery.Rows)
            {
                count += 1;
                if (dgvInvoiceSummery.Rows.Count>=count)
                {
                    dr.Cells[0].Value = count;
                }
            }
        }

        /// <summary>
        /// Inserting Spare Purchase Item details from the grid row by row.
        /// </summary>
        /// <param name="da">DataAccess class</param>
        private void InsertPurchaseInvoiceItemDetails(DataAccess da)
        {
            try
            {
                string query;
                int rowsAffacted = 0;
                decimal totalInvoiceValue = 0;

                for (int i = 0; i < dgvInvoiceSummery.Rows.Count; i++)
                {
                    totalInvoiceValue += decimal.Parse(dgvInvoiceSummery.Rows[i].Cells[4].Value.ToString());
                }

                for (int i = 0; i < dgvPurchaseInvoice.Rows.Count; i++)
                {
                    if (dgvPurchaseInvoice.Rows[i].Cells[0].Value.ToString() != "")
                    {
                        totalInvoiceValue += Convert.ToDecimal(dgvPurchaseInvoice.Rows[i].Cells[4].Value);

                        // Purchase Invoice Item Details
                        SqlParameter[] param = new SqlParameter[5];
                        param[0] = new SqlParameter("ItemQuantity", Convert.ToInt32(dgvPurchaseInvoice.Rows[i].Cells[2].Value));
                        param[1] = new SqlParameter("SparePartsPurchaseInvoiceNo", txtPurchaseInvoiceNo.Text);
                        param[2] = new SqlParameter("PartsNo", dgvPurchaseInvoice.Rows[i].Cells[0].Value.ToString());
                        param[3] = new SqlParameter("PartsUnitPrice", Convert.ToDecimal(dgvPurchaseInvoice.Rows[i].Cells[3].Value));
                        param[4] = new SqlParameter("PartTotalPrice", Convert.ToDecimal(dgvPurchaseInvoice.Rows[i].Cells[4].Value));

                        query = "INSERT INTO SparePartsPurchaseDetails (ItemQuantity, SparePartsPurchaseInvoiceNo, PartsNo, PartsUnitPrice,PartTotalPrice) VALUES (@ItemQuantity, @SparePartsPurchaseInvoiceNo, @PartsNo, @PartsUnitPrice,@PartTotalPrice)";
                        rowsAffacted = da.DoCommand(query, param);
                    }
                }

                query = "UPDATE SparePartsPurchaseRegister SET TotalInvoiceValue = " + totalInvoiceValue + " WHERE InvoiceNo='" + txtPurchaseInvoiceNo.Text + "'";
                da.DoCommand(query);
            }
            catch (Exception e)
            {
               
            }


        }

        private int GetAvailableQty(DataAccess da, string partsNo)
        {
            string query = "SELECT AvailableQty FROM InventoryMaster WHERE PartsNo='" + partsNo + "' AND isCurrent=1";
            DataTable dt = da.SelectCommand(query);
            if (dt.Rows.Count != 0)
            {
                return (int)dt.Rows[0][0];
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// If Parts No. doesn't exists then adding it to the database on Row leave event
        /// </summary>
        //private void dgvPurchaseInvoice_RowLeave(object sender, DataGridViewCellEventArgs e)
        //{
        //    DataAccess da = new DataAccess();
        //    string partsNo = dgvPurchaseInvoice.Rows[e.RowIndex].Cells[0].Value.ToString();

        //    if (partsNo != "")      // If Parts No. column is not empty
        //    {
        //        string query = "SELECT PartsName FROM SpareParts WHERE PartsNo='" + partsNo + "'";

        //        SqlDataReader dr;
        //        SqlCommand cmd = new SqlCommand(query, da.conn);
        //        da.conn.Open();
        //        dr = cmd.ExecuteReader();
        //        if (dr.HasRows == false)
        //        {
        //            dr.Close();
        //            SqlParameter[] param = new SqlParameter[4];
        //            param[0] = new SqlParameter("PartsNo", dgvPurchaseInvoice.Rows[e.RowIndex].Cells[0].Value.ToString());
        //            param[1] = new SqlParameter("PartsName", dgvPurchaseInvoice.Rows[e.RowIndex].Cells[1].Value.ToString());
        //            param[2] = new SqlParameter("Description", "");
        //            param[3] = new SqlParameter("BikeId", 1);

        //            string query1 = "INSERT INTO SpareParts (PartsNo, PartsName, Description, BikeId) VALUES (@PartsNo, @PartsName, @Description, @BikeId)";
        //            cmd = new SqlCommand(query1, da.conn);
        //            cmd.Parameters.AddRange(param);
        //            cmd.ExecuteNonQuery();
        //        }
        //    }

        //}

        private void pictureBox1_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void txtPurchaseInvoiceNo_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                CheckDuplicateInvoice();
            }
        }

        /// <summary>
        /// Checking Duplicate Purchase Invoice, If exists then not allow proceeding
        /// </summary>
        private void CheckDuplicateInvoice()
        {
            if (txtPurchaseInvoiceNo.Text != "")
            {
                DataAccess da = new DataAccess();
                string query = "SELECT InvoiceNo FROM SparePartsPurchaseRegister WHERE InvoiceNo='" + txtPurchaseInvoiceNo.Text + "'";
                SqlCommand cmd = new SqlCommand(query, da.conn);
                da.conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                
                if (dr.HasRows == true)
                {
                    da.conn.Close();
                    //txtPurchaseInvoiceNo.Text = "";
                    //txtPurchaseInvoiceNo.Focus();

                    DataTable dt = FillSupplierNameAndInvoiceDate(da, txtPurchaseInvoiceNo.Text);
                    txtSupplier.Text = (string)dt.Rows[0][2];
                    dateTimePicker1.Value = (DateTime)dt.Rows[0][0];
                    
                    //InsertPurchaseInvoiceItemDetails(da);
                    RefreshSummeryGrid();
                    // Showing Invoice Item Detail summery (saved only) into another grid


                    //ClearEntryGrid();
                    //ResetEntryGrid();
                }
            }
            //else
            //{
            //    MessageBox.Show("Please enter Invoice No.");
            //    txtPurchaseInvoiceNo.Focus();
            //}

        }

        private DataTable FillSupplierNameAndInvoiceDate(DataAccess da, string invoiceNo)
        {
            string query = "SELECT SparePartsPurchaseRegister.InvoiceDate, "
                            + "SparePartsPurchaseRegister.SupplierId, Supplier.Name "
                            + "FROM SparePartsPurchaseRegister INNER JOIN "
                            + "Supplier ON SparePartsPurchaseRegister.SupplierId = Supplier.SupplierId "
                            + "WHERE SparePartsPurchaseRegister.InvoiceNo='" + invoiceNo + "'";
            DataTable dt = da.SelectCommand(query);
            return dt;

        }

        private int GetSupplierIdByName(DataAccess da, string supplierName)
        {
            if (txtSupplier.Text != "")
            {
                string query = "SELECT SupplierId FROM Supplier WHERE Name='" + supplierName + "'";
                DataTable dt = da.SelectCommand(query);
                return (int)dt.Rows[0][0];
            }
            return 1;
        }

        // New Purchase Invoice
        private void pictureBox3_Click(object sender, EventArgs e)
        {
            ClearSummeryGrid();
            ClearEntryGrid();
            ResetEntryGrid();
            txtPurchaseInvoiceNo.Text = "";
            dateTimePicker1.ResetText();
            txtSupplier.Text = "";
            invoiceFlag = false;
        }

        private void dgvInvoiceSummery_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        private void dgvInvoiceSummery_Sorted(object sender, EventArgs e)
        {
            //int count = 1;
            //foreach (DataGridViewRow dr in dgvInvoiceSummery.Rows)
            //{

            //    if (dgvInvoiceSummery.Rows.Count != count)
            //    {
            //        dr.Cells[0].Value = count;
            //    }


            //    count += 1;

            //}
        }

        private void txtPurchaseInvoiceNo_Leave(object sender, EventArgs e)
        {
            CheckDuplicateInvoice();
        }

        private void txtPurchaseInvoiceNo_TextChanged(object sender, EventArgs e)
        {
            if (txtPurchaseInvoiceNo.Text.Length > 0)
            {
                txtPurchaseInvoiceNo.BackColor = Color.White;
            }
        }

        private void dgvPurchaseInvoice_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e)
        {
            try
            {
                
            }
            catch (Exception e1)
            { 
                
            }
        }

        private void dgvPurchaseInvoice_UserAddedRow(object sender, DataGridViewRowEventArgs e)
        {
            dgvPurchaseInvoice.Rows[e.Row.Index].Cells[0].Value = "";
            dgvPurchaseInvoice.Rows[e.Row.Index].Cells[1].Value = "";
            dgvPurchaseInvoice.Rows[e.Row.Index].Cells[2].Value = "";
            dgvPurchaseInvoice.Rows[e.Row.Index].Cells[3].Value = "";
            dgvPurchaseInvoice.Rows[e.Row.Index].Cells[4].Value = "";

            dgvPurchaseInvoice.AllowUserToAddRows = dgvPurchaseInvoice.RowCount <= 5;
        }

        private void dgvPurchaseInvoice_DefaultValuesNeeded(object sender, DataGridViewRowEventArgs e)
        {
           
        }

        ///// <summary>
        ///// Check Duplicate Entry
        ///// </summary>
        ///// <param name="sender"></param>
        ///// <param name="e"></param>
        //private void dgvPurchaseInvoice_CellLeave(object sender, DataGridViewCellEventArgs e)
        //{

        //    ArrayList alParts = new ArrayList();
        //    for (int i = 0; i < dgvPurchaseInvoice.Rows.Count; i++)
        //    {
        //        string partsNo = (string)dgvPurchaseInvoice.Rows[i].Cells[0].Value;
        //        if (partsNo != "")
        //        {
        //            if (!alParts.Contains(partsNo))
        //            {
        //                alParts.Add(partsNo);
        //            }
        //            else
        //            {
        //                MessageBox.Show("You already entered this Part No");

        //            }
        //        }
        //    }
        //}



    }
}